package com.lemon.exam.repository;

import com.lemon.exam.entity.po.PaperPO;
import com.lemon.exam.entity.vo.PaperVO;
import org.springframework.data.r2dbc.repository.Query;
import org.springframework.data.r2dbc.repository.R2dbcRepository;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;


/**
 * 试卷表 数据访问接口
 *
 * @author Lemon
 * @since 2025/05/11 13:47:13
 */
public interface PaperRepository extends R2dbcRepository<PaperPO, Long> {
    @Query("SELECT origin_file_name, file_path FROM biz_paper WHERE id = :id LIMIT 1")
    Mono<PaperPO> findFileNameFilePathById(Long id);

    @Query("SELECT create_id, start_time, end_time FROM biz_paper WHERE id = :id LIMIT 1")
    Mono<PaperPO> findCreateIdAndStartTimeById(Long id);

    @Query("""
            SELECT
               p.id,
               p.`name`,
               p.remark,
               p.start_time,
               p.end_time,
               p.duration,
               p.pass_score,
               p.total_score,
               CONCAT( g.`name`, ' [', c.`name`, ']' ) AS grade_class_name
            FROM
               biz_paper p
               LEFT JOIN biz_grade g ON p.grade_id = g.id
               LEFT JOIN biz_class c ON p.class_id = c.id
            WHERE
               p.deleted = 0 AND p.end_time > NOW()
               AND p.class_id IN ( SELECT class_id FROM biz_class_user WHERE user_id = :userId )
               AND p.id NOT IN ( SELECT paper_id FROM biz_paper_answer WHERE user_id = :userId )
            ORDER BY
               p.create_time DESC
            LIMIT :pageNum, :pageSize
            """)
    Flux<PaperVO> findPaddingAnswerPaperByPage(Long userId, int pageNum, int pageSize);

    @Query("""
            WITH user_classes AS (
                SELECT DISTINCT class_id
                FROM biz_class_user
                WHERE user_id = :userId
            )
            SELECT
                p.id,
                p.`name`,
                p.remark,
                p.start_time,
                p.end_time,
                p.duration,
                p.pass_score,
                p.total_score,
                CONCAT(g.`name`, ' [', c.`name`, ']') AS grade_class_name,
                bpa.total_score AS user_score
            FROM
                biz_paper p
                INNER JOIN biz_paper_answer bpa ON p.id = bpa.paper_id AND bpa.user_id = :userId
                LEFT JOIN biz_grade g ON p.grade_id = g.id
                LEFT JOIN biz_class c ON p.class_id = c.id
                INNER JOIN user_classes uc ON p.class_id = uc.class_id
            WHERE
                p.deleted = 0
            ORDER BY
                p.create_time DESC
            LIMIT :pageNum, :pageSize
            """)
    Flux<PaperVO> findFinishAnswerPaperByPage(Long userId, int pageNum, int pageSize);

    @Query("""
            SELECT
               p.id,
               p.`name`,
               p.remark,
               p.start_time,
               p.end_time,
               p.duration,
               p.pass_score,
               p.total_score,
               CONCAT( g.`name`, ' [', c.`name`, ']' ) AS grade_class_name
            FROM
               biz_paper p
               LEFT JOIN biz_grade g ON p.grade_id = g.id
               LEFT JOIN biz_class c ON p.class_id = c.id
            WHERE
               p.deleted = 0 AND p.end_time < NOW()
               AND p.class_id IN (SELECT class_id FROM biz_class_user WHERE user_id = :userId)
               AND p.id NOT IN (SELECT paper_id FROM biz_paper_answer WHERE user_id = :userId)
            ORDER BY
               p.create_time DESC
            LIMIT :pageNum, :pageSize
            """)
    Flux<PaperVO> findNotFillAnswerPaperByPage(Long userId, int pageNum, int pageSize);

    @Query("""
            SELECT
               p.id,
               p.`name`,
               p.remark,
               p.start_time,
               p.end_time,
               p.duration,
               p.pass_score,
               p.total_score,
               CONCAT( g.`name`, ' [', c.`name`, ']' ) AS grade_class_name
            FROM
               biz_paper p
               LEFT JOIN biz_grade g ON p.grade_id = g.id
               LEFT JOIN biz_class c ON p.class_id = c.id
            WHERE
               p.deleted = 0 AND p.end_time < NOW() AND create_id = :userId
               AND EXISTS (SELECT 1 FROM biz_paper_answer pa WHERE p.id = pa.paper_id AND pa.passed IS NULL)
            ORDER BY
               p.create_time DESC
            LIMIT :pageNum, :pageSize
            """)
    Flux<PaperVO> findPaddingGradingPaperByPage(Long userId, int pageNum, int pageSize);

    @Query("""
            SELECT
               p.id,
               p.`name`,
               p.remark,
               p.start_time,
               p.end_time,
               p.duration,
               p.pass_score,
               p.total_score,
               CONCAT( g.`name`, ' [', c.`name`, ']' ) AS grade_class_name
            FROM
               biz_paper p
               LEFT JOIN biz_grade g ON p.grade_id = g.id
               LEFT JOIN biz_class c ON p.class_id = c.id
            WHERE
               p.deleted = 0 AND p.end_time < NOW() AND create_id = :userId
               AND EXISTS (SELECT 1 FROM biz_paper_answer pa WHERE p.id = pa.paper_id AND pa.passed IS NOT NULL)
            ORDER BY
               p.create_time DESC
            LIMIT :pageNum, :pageSize
            """)
    Flux<PaperVO> findFinishGradingPaperByPage(Long userId, int pageNum, int pageSize);

    @Query("""
            SELECT
            	id, class_id, name, remark, start_time, end_time, duration, look_answer, upset, file_path, pass_score, total_score
            FROM biz_paper WHERE id = :id LIMIT 1
            """)
    Mono<PaperPO> findPaperById(Long id);

    @Query("SELECT end_time FROM biz_paper WHERE id = :id LIMIT 1")
    Mono<PaperPO> findEndTimeById(Long id);

    @Query("""
            SELECT
                id, pass_score, total_score, file_path
            FROM biz_paper
            WHERE id = (SELECT paper_id FROM biz_paper_answer WHERE id = :answerId LIMIT 1)
            LIMIT 1
            """)
    Mono<PaperPO> findPassScoreFilePathByAnswerId(Long answerId);
}
